﻿Module MODweb

    Public _
        initSQLStr As String, _
        finalSQLStr As String

    Public _
        isNewRecord As Boolean, _
        bUpdatedFromFile As Boolean

    'Objects -------------------------------------------------------------------------------------------------------

    Private fso As Scripting.FileSystemObject
    Private oSQL As Scripting.TextStream
    Private oLog As Scripting.TextStream
    Private oCLS_IE As CLS_IE

    ' User defined objects ---------------------------------------------------------------------------------------------------

    Private Structure SQLstatementList
        Dim uniqueID As String
        Dim SQLstatement As String
    End Structure

    Private statementArray() As SQLstatementList

    Private iArrayLength As Integer

    ' Constants -------------------------------------------------------------------------------------------------------------------------------

    Private Const sUPDATE_DIR_NAME As String = "update"

    ' Booleans --------------------------------------------------------------------------------------------------------------------------------

    Private bFatalError As Boolean

    ' Strings -------------------------------------------------------------------------------------------------------------------------------

    Private _
        SQLclause As String, _
        sActionOnError As String, _
        statementListString As String

    ' Init Web Module ---------------------------------------------------------------------------------------------------------------------

    Public Sub init()

        resetStatementArray()

    End Sub

    Public Sub updateAllRecords(Optional ByVal sSQLin As String = "")

        On Error Resume Next

        logMessage( _
            iDEBUG, _
            "Web", _
            "updateAllRecords method called ")

        Dim _
            sSQL As String, _
            sTableName As String, _
            sPostData As String, _
            sURL As String

        If sSQLin = "" Then

            If iArrayLength < 1 Then ' In case there are no items in the SQLstatement list

                MsgBox("There are no edited records to update")

                Exit Sub

            End If

            sSQL = buildStatementListString()

            logMessage( _
                iDEBUG, _
                "Web", _
                "buildStatementListString method called ", _
                "Sql = " & sSQL)

        Else

            sSQL = sSQLin

        End If

        ' Show modal 'updating' dialog --------------------------------------------------------------------------------

        If IsNumeric(FRMmain.LISTtables.SelectedIndex) And FRMmain.LISTtables.SelectedIndex > -1 Then

            sTableName = FRMmain.LISTtables.Items(FRMmain.LISTtables.SelectedIndex)

        Else

            sTableName = "components"

        End If

        sPostData = "SQLstatementList=" & sSQL

        sPostData = Replace(sPostData, " ", "%20")
        sPostData = Replace(sPostData, "&", "%26")
        sPostData = sPostData & "&action=multipleUpdate"
        sPostData = sPostData & "&tableName=" & sTableName

        sURL = getPref("remoteDB", "serverAddress") & "/" & getPref("remoteDB", "updatePagePath")

        ' Send data to web browser for update -------------------------------------------------------------------------------------------

        oCLS_IE = New CLS_IE

        oCLS_IE.updateWithBrowser(sURL, sPostData)

        FRMmain.bRemoteSaved = True

    End Sub

    Public Function getPostData(ByVal sData As String)

        'On Error Resume Next

        'Dim aBuffer() As Byte, sBuffer$

        'sBuffer$ = sData

        'aBuffer = StrConv(sBuffer$, VbStrConv.Wide)

        'getPostData = aBuffer
        Throw New NotImplementedException
    End Function

    Public Function buildStatementListString()

        On Error Resume Next

        statementListString = ""

        For i = 0 To iArrayLength - 1
            statementListString = _
               statementListString & _
               statementArray(i).uniqueID & _
               ";;" & _
               statementArray(i).SQLstatement & _
               "||"
        Next

        ' Remove the last two 'pipes' ------------------------------------------------------------------------------------------------

        buildStatementListString = Utils.Left(statementListString, Len(statementListString) - 2)

    End Function


    Public Sub addSQLlistItem(ByVal uniqueID As String, ByVal SQLstatement As String)

        'On Error Resume Next

        '' If the unique ID exists in the array, then replace its SQL statement with the new one, unless the existing SQLstatment operation is 'INSERT INTO' ---------------------------------------------

        'For i = 1 To iArrayLength

        '    If statementArray(i - 1).uniqueID = uniqueID And _
        '        UCase(Left(statementArray(i - 1).SQLstatement, 11)) <> "INSERT INTO" Then

        '        statementArray(i - 1).SQLstatement = SQLstatement

        '        Exit Sub

        '    End If

        'Next

        'ReDim Preserve statementArray(iArrayLength)

        'statementArray(iArrayLength).uniqueID = uniqueID
        'statementArray(iArrayLength).SQLstatement = SQLstatement

        'iArrayLength = iArrayLength + 1

        'FRMmain.WebUpdateAllRecords.Enabled = True
        'FRMmain.WebUpdateLater.Enabled = True
        'FRMmain.editUpdateRow.Enabled = True
        Throw New NotImplementedException
    End Sub

    Public Sub resetStatementArray()

        ReDim statementArray(0)
        iArrayLength = 0

    End Sub

    Public Function buildUpdateStatement(ByVal objRecordset As ADODB.Recordset, ByVal clause As String)

        'On Error Resume Next

        '' ths variable is checked before the user exits to make sure he updated the remote database
        '' or saved the list of records to update.
        '' It is set to true, if the database is updated, or if the records are saved

        'FRMmain.bRemoteSaved = False

        'SQLclause = clause

        'If objRecordset.EOF Then Exit Function

        'remoteDBfieldNames = MODpreferences.getPref("remoteDB", "fieldNames")

        'remoteDBfieldNamesArray = Split(remoteDBfieldNames, ",")

        'Select Case clause
        '    Case "WHERE"
        '        initSQLStr = " WHERE [uniqueID] = '" & objRecordset("uniqueID") & "'"

        '        '            For i = 1 To UBound(remoteDBfieldNamesArray) - 1
        '        '                initSQLStr = initSQLStr & " AND [" & remoteDBfieldNamesArray(i) & "] = '" & _
        '        '                    objRecordset(remoteDBfieldNamesArray(i)) & "'"
        '        '            Next

        '    Case "SET"
        '        finalSQLStr = "UPDATE " & FRMmain.LISTtables.Items(FRMmain.LISTtables.SelectedIndex) & "" & _
        '            " SET [" & remoteDBfieldNamesArray(0) & "] = '" & objRecordset(remoteDBfieldNamesArray(0)) & "'"

        '        For i = 1 To UBound(remoteDBfieldNamesArray)
        '            finalSQLStr = finalSQLStr & " , [" & remoteDBfieldNamesArray(i) & "] = '" & _
        '                objRecordset(remoteDBfieldNamesArray(i)) & "'"
        '        Next

        '        finalSQLStr = finalSQLStr & initSQLStr

        '        addSQLlistItem(objRecordset("uniqueID"), finalSQLStr)

        '    Case "INSERT"

        '        For i = 0 To UBound(remoteDBfieldNamesArray)
        '            sFieldNames = sFieldNames & remoteDBfieldNamesArray(i) & ","
        '        Next

        '        sFieldNames = Utils.Left(sFieldNames, Len(sFieldNames) - 1)

        '        For i = 0 To UBound(remoteDBfieldNamesArray)
        '            sFieldValues = sFieldValues & "'" & objRecordset(remoteDBfieldNamesArray(i)) & "',"
        '        Next

        '        sFieldValues = Utils.Left(sFieldValues, Len(sFieldValues) - 1)

        '        finalSQLStr = "INSERT INTO " & _
        '            FRMmain.LISTtables.Items(FRMmain.LISTtables.SelectedIndex) & _
        '            " ( " & _
        '            sFieldNames & _
        '            " ) VALUES ( " & _
        '            sFieldValues & _
        '            " ) "

        '        '------------------------------------------------------------------------------
        '        ' Only check the autoUpdate preference if it is a new record,
        '        ' because if it is not a new row then the user must choose to
        '        ' update the remote record manually by pressing "OK" on the confirm dialog
        '        '------------------------------------------------------------------------------

        '        If isNewRecord Then

        '            addSQLlistItem(objRecordset("uniqueID"), finalSQLStr)

        '            ' Records are automatically updated after insertion because otherwise once the user edits the new row, the statement will be built with a 'WHERE' clause

        '            'updateAllRecords

        '        End If

        '    Case "DELETE"
        '        finalSQLStr = "DELETE * FROM " & _
        '            FRMmain.LISTtables.Items(FRMmain.LISTtables.SelectedIndex) & _
        '            " WHERE " & _
        '            "uniqueID = '" & _
        '            objRecordset("uniqueID") & _
        '            "'"

        '        addSQLlistItem(objRecordset("uniqueID"), finalSQLStr)

        'End Select

        Throw New NotImplementedException
    End Function

    ' Update remote database with SQL staments that were written to a text file -------------------------------------------------------------

    Public Sub updateFromFile()

        'On Error Resume Next

        'bUpdatedFromFile = True

        'Dim sSQL As String

        'fso = New Scripting.FileSystemObject

        'If Not fso.FileExists(MODweb.sRemoteUpdateRecordsFilePath) Then

        '    MsgBox("There is no file to read from")

        '    Exit Sub

        'End If

        'On Error Resume Next

        'oSQL = fso.OpenTextFile(MODweb.sRemoteUpdateRecordsFilePath, Scripting.FileUse.ForReading, False)

        'If Err.Number <> 0 Then

        '    MsgBox( _
        '        "Could not open records file for updating." & _
        '        vbCrLf & _
        '        "Reason : " & _
        '        Err.Description)

        '    logMessage( _
        '        iERROR, _
        '        "Web", _
        '        "Could not open text file for reading records for update", _
        '        , _
        '        Err.Number, _
        '        Err.Description)

        '    Exit Sub

        'Else

        '    logMessage( _
        '        iINFO, _
        '        "Web", _
        '        "Opened text file for reading records for update")

        'End If

        'On Error Resume Next

        'sSQL = oSQL.ReadAll

        'If Err.Number <> 0 Then

        '    MsgBox( _
        '        "Could not read records file for updating." & _
        '        vbCrLf & _
        '        "Reason : " & _
        '        Err.Description)

        '    logMessage( _
        '        iERROR, _
        '        "Web", _
        '        "Could not read text file for update", _
        '        , _
        '        Err.Number, _
        '        Err.Description)

        '    Exit Sub

        'Else

        '    logMessage( _
        '        iINFO, _
        '        "Web", _
        '        "Read text file for update", _
        '        "SQL = " & sSQL)

        'End If

        'sSQL = Replace(sSQL, vbCrLf, "||")

        '' check for extra pipes and remove them -------------------------------------------------------------------------------

        'If Right(sSQL, 2) = "||" Then sSQL = Utils.Left(sSQL, Len(sSQL) - 2)

        'If Trim(sSQL) = "" Then

        '    MsgBox("File is empty")

        '    Exit Sub

        'End If


        'oSQL.Close()

        'updateAllRecords(sSQL)
        Throw New NotImplementedException
    End Sub

    ' Write SQl statements to a text file so the user can update the remote database at a later time --------------------------------------------------------

    Public Sub exportRecordsForUpdate()

        On Error Resume Next

        Dim sSQL As String

        If iArrayLength = 0 Then ' In case there are no items in the SQLstatement list

            MsgBox("There are no edited records to update")

            Exit Sub

        End If

        sSQL = buildStatementListString()

        sSQL = Replace(sSQL, "||", vbCrLf)

        If Trim(sSQL) = "" Then

            MsgBox("Could not save records for updating")

            Exit Sub

        End If

        fso = New Scripting.FileSystemObject

        If Not fso.FolderExists(sRemoteUpdateDirPath) Then fso.CreateFolder(sRemoteUpdateDirPath)

        On Error Resume Next

        oSQL = fso.OpenTextFile(MODweb.sRemoteUpdateRecordsFilePath, Scripting.FileUse.ForAppending, True)

        If Err.Number <> 0 Then

            MsgBox( _
                "Could not open records file for saving." & _
                vbCrLf & _
                "Reason : " & _
                Err.Description)

            logMessage( _
                iERROR, _
                "Web", _
                "Could not open text file for saving records for update", _
                , _
                Err.Number, _
                Err.Description)

            Exit Sub

        Else

            logMessage( _
                iINFO, _
                "Web", _
                "Opened text file for saving records for update")

        End If

        ' Write SQL statements to text file ----------------------------------------------------------------------------------------------

        On Error Resume Next

        oSQL.Write(sSQL & vbCrLf)

        If Err.Number <> 0 Then

            MsgBox( _
                "Could not write to save file." & _
                vbCrLf & _
                "Reason : " & _
                Err.Description)

            logMessage( _
                iERROR, _
                "Web", _
                "Could not write to text file for saving records", _
                "SQL = " & sSQL, _
                Err.Number, _
                Err.Description)

            Exit Sub

        Else

            logMessage( _
                iINFO, _
                "Web", _
                "Wrote records to text file for saving", _
                "SQL = " & sSQL)

        End If

        oSQL.Close()

        oSQL = Nothing

        fso = Nothing

        FRMmain.bRemoteSaved = True

        resetStatementArray()

        MsgBox("Records saved")

    End Sub

    Public ReadOnly Property sRemoteUpdateDirPath() As String
        Get
            sRemoteUpdateDirPath = _
                Application.ExecutablePath & "/" & _
                sUPDATE_DIR_NAME & _
                "/"
        End Get
    End Property

    Public ReadOnly Property sRemoteUpdateRecordsFilePath() As String

        Get
            sRemoteUpdateRecordsFilePath = _
        Application.ExecutablePath & _
        "/" & _
        sUPDATE_DIR_NAME & _
        "/webSQL.update"

        End Get


    End Property

    Public ReadOnly Property sRemoteUpdateBackupDirPath()
        Get
            sRemoteUpdateBackupDirPath = _
                    Application.ExecutablePath & _
                    "/" & _
                    sUPDATE_DIR_NAME & _
                    "/" & _
                    "backup" & _
                    "/"
        End Get

    End Property
End Module
